Calcolare le ore di lavoro con Excel
Frequentemente nei fogli di lavoro aziendali si rende
necessario calcola la differenza di ore (es. uscita meno entrata).
In questa sezione viene descritto come creare un Foglio di lavoro Excel che permetta di calcolare
le ore di lavoro settimanali e il compenso
dovuto, incluse le eventuali ore straordinarie.
Viene anche trattata l'ipotesi
che la differenza fra gli orari di entrata e uscita non siano riferiti alla
stessa data.
Impostazione di una tabella per calcolare le ore di lavoro
A titolo di esempio viene proposto un calcolo delle ore di lavoro svolte su una base settimanale, ovviamente l'esempio può facilmente essere declinato su un periodo di tempo di durata diversa.
Nell'esempio sotto illustrato, per ragioni di spazio è stata sostituita la data estesa (Lunedì 9
febbraio 2015) trasformandola nel formato numerico personalizzato ggg gg/mm
dove le prime tre lettere "g" rappresentano il giorno abbreviato e i due successivi
valori "gg" e "mm" il giorno e il mese in formato numerico. Il risultato di
questa formattazione personalizzata visualizza la data nel seguente formato: lun 09/02
Nelle colonne C,D,E,F vanno inseriti gli
orari di entrata e uscita dal lavoro.
:: ATTENZIONE. Per imputare correttamente un'informazione numerica in formato ora vanno utilizzati i due punti “:” per separare ore, minuti e secondi.
Ad esempio digitando 6:30:00 i valori immessi verranno automaticamente riconosciute da Excel come formato numerico ora.
Qualora dovessero essere imputati anche i centesimi di secondo, vanno inseriti
dopo una virgola. Esempio 6:29:59,52
Impostazione formule per il calcolo del totale ore giornaliere e settimanali
Nell'esempio viene ipotizzato un orario di lavoro spezzato mattina -
pomeriggio, nel totalizzare le ore della giornata sarà
necessario improntare una Formula che preveda di sommare la differenza
fra l'orario di uscita e di entrata del mattino e del pomeriggio.
Nel caso dell'orario del lunedì imputato nella cella H3, va
inserita la Formula =D3-C3+F3-E2:E3
Ovviamente è possibile trascinare con il pulsante di riempimento la
Formula nelle celle sottostanti per ottenere le successive giornate.
Nella cella H11 è inserita un funzione di sommatoria
=SOMMA(H3:H9)
Attenzione! Il risultato che viene restituito nella cella H11
risulta apparentemente sbagliato. In realtà il formato numerico ORA quando arriva
al valore 24 si azzera. Di conseguenza al totale delle ore 17:20 mancano la somma
delle prime 24 ore.
Per formattare correttamente il risultato, nella
finestra di dialogo Formato celle cliccare sulla
scheda Numero.
All'interno della scheda Numero, nella colonna a sinistra selezionare il
formato Ora e nella colonna a destra scegliere il
formato 37:50:55, che serve appositamente per il conteggio
delle ore.
Applicando questa formattazione nella cella H11 si visualizza il corretto
risultato 41:20
Per un approfondimento sul formato ora: Excel: formato ora. Funzioni di base per calcolare e sommare ore minuti
:: Per aprire la finestra di dialogo formato celle, nella scheda HOME nel gruppo pulsanti Numeri, cliccare in basso destra sul pulsante di apertura della finestra di dialogo
Come calcolare la differenza fra orari, se l'orario di uscita fosse un giorno successivo a quello dell'entrata
Qualora si verificasse un orario di uscita nel giorno
successivo all'orario dell'entrata, si può procedere in due distinti modi. Il
primo è utilizzare un formato data associato all'ora, in questo modo Excel
individua la corretta differenza fra i due orari.
Il secondo metodo, più semplice, consiste nell'applicazione di una Funzione SE,
che consideri se l'ora di uscita è minore di quella di entrata. Nello specifico
(vedi figura sottostante) nella colonna A è inserita l'entrata e nella B
l'uscita. la formula imputata nella cella C1 è la seguente
=SE(B2<A2;B2+1;B2)-A2
Questa Funzione Logica valuta se l'orario inserito nella cella "uscita" è minore
del valore inserito nella cella "entrata". Se questa condizione fosse vera la
formula applicata sarebbe B2+1-A2
Ovviamente trascinando la Funzione SE nelle celle sottostanti, qualora l'uscita
fosse nello stesso giorno dell'entrata, la formula applicata sarebbe
semplicemente B2-A2
Trasformare il formato ora in numero
Una volta totalizzato il monte ore è necessario trasformare il
formato ora in un formato numerico che permetta di poterlo moltiplicare
per il valore del compenso orario.
Excel salva il formato ora in un
numero seriale che va da 0 a 1 per l'arco delle 24 ore. Non a caso 1 è
in Excel il valore numerico seriale dell'unità tempo giorno.
Dal punto di
vista pratico si deve semplicemente moltiplicare il totale del monte ore per
24, in questo modo la suddivisione sessageimale dei minuti e secondi del
formato ora viene trasformata nell'equivalente numerico centesimale.
Il valore 41:20 moltiplicato per 24 restituisce il numero 41,33, questo
numero può essere successivamente moltiplicato per la retribuzione
oraria.
Per un approfondimento si rimanda a:
Excel: formato ora,
funzioni di base,
calcolare e sommare ore minuti
Calcolare il compenso e applicazione di una retribuzione straordinaria
Allo scopo viene ipotizzato il seguente esempio:
si ipotizza che le prime 36 ore settimanali siano
retribuite a € 17,50, mentre le eventuali ore successive ad € 19,00
Dal punto di vista del calcolo, nel caso di un monte ore settimanale di 41:20 le prime
36 ore saranno moltiplicate per 17,50 e la differenza (41:20 meno 36:00)
sarà moltiplicata per 19,00
nel caso però che il totale delle ore non superasse le 36 il calcolo
dello stipendio sarà dato dalla sola operazione di moltiplicare il
totale ore per la retribuzione ordinaria.
Essendoci due condizioni diverse
è necessario impostare un formula SE il cui test logico verifichi se il
totale delle ore settimanali ha superato il limite delle 36 ore. nella
successiva espressione del vero e falso vera rispettivamente formulato
un calcolo che ipotizzi il conteggio dello straordinario oppure la sola
retribuzione ordinaria.
La funzione SE sarà espressa come segue: =SE(H12>36;C12*36+(H12-36)*C13;H12*C12)
Per un approfondimento sul formato ora, è disponibile un video caricato sul
nuovo canale YouTube
Se fossi interessato a seguire successivi tutorial su Excel poi iscriverti al
canale.
